/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Bussiness;

//import Connection.Admin;
import Connection.DBUtil;
import Connection.Employee;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 *
 * @author SONY
 */
public class EmployeeDAO {
    private static Connection conn = null;
    private static CallableStatement cs = null;
    private static ResultSet rs = null;
    private static Employee objEmployee = null;
    private static DBUtil db = new DBUtil();
    private static Vector project;
    
    public EmployeeDAO(){
    }
    
    public static void ChangePassword(String oldPassword, String newPassword){
        try{
            conn = db.connect();
            cs = conn.prepareCall("{call spChangePasswordEmployee(?,?)}");
            cs.setString(1, oldPassword);
            cs.setString(2, newPassword);
            cs.executeUpdate();
        } catch(Exception ex){
            ex.printStackTrace();
        }
    }
    
    public static Employee getLoginEmployee(String email){
        try
        {
        conn = db.connect();
        cs = conn.prepareCall("{call spGetEmployee(?)}");
        cs.setString(1, email);
        rs = cs.executeQuery();
        while(rs.next()){
            objEmployee = new Employee();
            /*objEmployee.setStt(rs.getInt(1));
            objEmployee.setEmployeeID(rs.getString(2));*/
            objEmployee.setEmployeeName(rs.getString(3));
            /*objEmployee.setGender(rs.getBoolean(4));
            objEmployee.setDayOfBirth(rs.getDate(5));
            objEmployee.setPhone(rs.getString(6));
            objEmployee.setAddress(rs.getString(7));*/
            objEmployee.setEmail(rs.getString(8));
            objEmployee.setPassword(rs.getString(9));
            /*objEmployee.setWorkExperience(rs.getString(10));
            objEmployee.setLocationID(rs.getString(11));
            objEmployee.setDepartmentID(rs.getString(12));
            objEmployee.setProjectID(rs.getString(13));*/
        }
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
        return objEmployee;
    }
    
    public static void insertEmployeeToDB(Employee objEmployee1) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spInsertEmployee(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
        cs.setString(1, objEmployee1.getEmployeeID());
        cs.setString(2, objEmployee1.getEmployeeName());
        cs.setBoolean(3, objEmployee1.getGender());
        cs.setDate(4, new java.sql.Date(objEmployee1.getDayOfBirth().getTime()));
        cs.setString(5, objEmployee1.getPhone());
        cs.setString(6, objEmployee1.getAddress());
        cs.setString(7, objEmployee1.getEmail());
        cs.setString(8, objEmployee1.getPassword());
        cs.setInt(9, objEmployee1.getWorkExperience());
        cs.setString(10, objEmployee1.getLocationID());
        cs.setString(11, objEmployee1.getDepartmentID());
        cs.setString(12, objEmployee1.getProjectID());
        cs.executeUpdate();
        conn.close();
    }
   
    public static void upDateEmployee(Employee objEmployee1, String EmployeeID) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spUpdateEmployee(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
        cs.setString(1, EmployeeID);   
        //cs.setString(2, objEmployee1.getEmployeeID());
        cs.setString(2, objEmployee1.getEmployeeName());
        cs.setBoolean(3, objEmployee1.getGender());
        cs.setDate(4, new java.sql.Date(objEmployee1.getDayOfBirth().getTime()));
        cs.setString(5, objEmployee1.getPhone());
        cs.setString(6, objEmployee1.getAddress());
        cs.setString(7, objEmployee1.getEmail());
        cs.setString(8, objEmployee1.getPassword());
        cs.setInt(9, objEmployee1.getWorkExperience());
        cs.setString(10, objEmployee1.getLocationID());
        cs.setString(11, objEmployee1.getDepartmentID());
        cs.setString(12, objEmployee1.getProjectID());
        cs.executeUpdate();
        conn.close();
    }
            
    public static void deleteToDB(String EmployeeID) throws SQLException{
        String SQL;
        DBUtil objDBUtil = new DBUtil();
        Connection conn = objDBUtil.connect(); 
        SQL = "DELETE FROM Employee WHERE EmployeeID=?";
        PreparedStatement pstmt = conn.prepareStatement(SQL);
        //String DepartmentID = tbBang.getValueAt(tbBang.getSelectedRow(), 1).toString();
        pstmt.setString(1, EmployeeID);
        pstmt.executeUpdate();
        conn.close();
    }
    
    public static Vector getComboboxEmployee() throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spEmployee}");
        rs = cs.executeQuery();
        project = new Vector();
        project.add("Select");
        int count = 0;
        while(rs.next()){
            int index = 0;
            for (int i = 0; i < count; i++) {
                if (rs.getString(3).equals(project.get(i))) {
                    index++;
                }
            }
            if (index == 0) {
                project.addElement(rs.getString(3));
                count++;
            }
        }
        return project;
    }
    
    public static Vector getComboboxEmployeeID() throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spEmployee}");
        rs = cs.executeQuery();
        project = new Vector();
        project.add("Select");
        while (rs.next()) {            
            project.addElement(rs.getString(2));
        }
        return project;
   }
    
    public static Vector getComboboxEmployeeSelect() throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spEmployee}");
        rs = cs.executeQuery();
        project = new Vector();
        project.add("Select Employee Name...");
        int count = 0;
        while(rs.next()){
            int index = 0;
            for (int i = 0; i < count; i++) {
                if (rs.getString(3).equals(project.get(i))) {
                    index++;
                }
            }
            if (index == 0) {
                project.addElement(rs.getString(3));
                count++;
            }
        }
        return project;
    }
    
    public static Employee getForTransfer(String EmployeeID) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spSearchEmployeeByID(?)}");
        cs.setString(1, EmployeeID);
        rs = cs.executeQuery();
        objEmployee = new Employee();
        while (rs.next()) {
            objEmployee.setLocationID(rs.getString(11));
            objEmployee.setDepartmentID(rs.getString(12));
            objEmployee.setProjectID(rs.getString(13));
        }
        return objEmployee;
    }
    
    public static String getEmployeeName(String EmployeeID) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spSearchEmployeeByID(?)}");
        cs.setString(1, EmployeeID);
        rs = cs.executeQuery();
        String EmployeeName = null;
        while (rs.next()) {
            EmployeeName = rs.getString(3);
        }
        return EmployeeName;
    }
    
    public static String getEmployeeID(String EmployeeName) throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spGetEmployeeID(?)}");
        cs.setString(1, EmployeeName);
        rs = cs.executeQuery();
        String EmployeeID = null;
        while(rs.next()){
            EmployeeID = rs.getString(1);
        }
        return EmployeeID;
    }
    
    
    public static int countTotalEmployee() throws SQLException{
        conn = db.connect();
        cs = conn.prepareCall("{call spEmployee}");
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }
    
    public static Employee searchEmployeeByID(String EmployeeID) throws SQLException{
        conn = db.connect();
        cs =conn.prepareCall("{call spSearchEmployeeByID(?)}");
        cs.setString(1, EmployeeID);
        rs = cs.executeQuery();
        objEmployee = new Employee();
        while (rs.next()) {            
            objEmployee.setStt(rs.getInt(1));
            objEmployee.setEmployeeID(rs.getString(2));
            objEmployee.setEmployeeName(rs.getString(3));
            objEmployee.setGender(rs.getBoolean(4));
            objEmployee.setDayOfBirth(rs.getDate(5));
            objEmployee.setPhone(rs.getString(6));
            objEmployee.setAddress(rs.getString(7));
            objEmployee.setEmail(rs.getString(8));
            objEmployee.setWorkExperience(rs.getInt(10));
            objEmployee.setPassword(rs.getString(9));
            objEmployee.setLocationID(rs.getString(11));
            objEmployee.setDepartmentID(rs.getString(12));
            objEmployee.setProjectID(rs.getString(13));
        }
        return objEmployee;
    }
    
    public static int countSearchEmployeeByName(String EmployeeName) throws SQLException{
        conn = db.connect();
        cs =conn.prepareCall("{call spSearchEmployeeByName(?)}");
        cs.setString(1, EmployeeName);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()){
            count++;
        }
        return count;
    }
    
    public static int countSearchEmployeeByProject(String projectID) throws SQLException{
        conn = db.connect();
        cs =conn.prepareCall("{call spSearchEmployeeByProject(?)}");
        cs.setString(1, projectID);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()){
            count++;
        }
        return count;
    }
    
    public static int countSearchEmployeeByLocation(String locationID) throws SQLException{
        conn = db.connect();
        cs =conn.prepareCall("{call spSearchEmployeeByLocationID(?)}");
        cs.setString(1, locationID);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()){
            count++;
        }
        return count;
    }
    
    public static int countSearchEmployeeByDepartment(String departmentID) throws SQLException{
        conn = db.connect();
        cs =conn.prepareCall("{call spSearchEmployeeByDepartmentID(?)}");
        cs.setString(1, departmentID);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()){
            count++;
        }
        return count;
    }
}
